<?php
class Shinkensaku_Model_Selrail extends Shinkensaku_Model_Base
{
	/**
	 * 表示用変換
	 * @param $railList
	 * @return 変換後リスト
	 */
	public function convertData($railList)
    {
    	$DispList = array();

    	// カナ配列作成
        $kana50onList = array(
        	'ｱｲｳｴｵ', 'ｶｷｸｹｺ', 'ｻｼｽｾｿ', 'ﾀﾁﾂﾃﾄ', 'ﾅﾆﾇﾈﾉ', 'ﾊﾋﾌﾍﾎ', 'ﾏﾐﾑﾒﾓ', 'ﾔﾕﾖ', 'ﾗﾘﾙﾚﾛ', 'ﾜ'
        );

        $strTmpKana = '';
        $strTmpKanaBk = '';
        $indexNo = 0;

        foreach ($railList as $railData)
        {
        	$ensenKana = $railData['ENSEN_KANA'];
        	$ensenName = $railData['ENSEN_NAME'];
        	$ensenKanaFirst = mb_substr($ensenKana, 0, 1);

        	foreach ($kana50onList as $kana50onData)
        	{
        		$result = strpos($kana50onData, $ensenKanaFirst);

        		if ($result !== false)
        		{
	        		$strTmpKana = $kana50onData;

	        		if ($strTmpKana != $strTmpKanaBk)
	        		{
	        			$DispList[] = array(
// 	        			    'KanaFirst' => substr($strTmpKana, 0, 1),
	        				'KanaFirst' => mb_substr($strTmpKana, 0, 1),
	        			    'List'  => array()
	        			);
	        			$strTmpKanaBk = $strTmpKana;
	        			$indexNo = count($DispList) - 1;
	        			break;
	        		}
        		}
        	}

        	$railData['ENSEN_NAME'] = str_replace("片町線", "片町線（学研都市線）", $ensenName);
        	$DispList[$indexNo]['List'][] = $railData;
        }
        return $DispList;
    }

    /**
     * sql文生成（OemCdなし）
     * @param $params
     * @param $db_params
     * @return sql文
     */
	protected function generateSql($params, &$db_params)
	{
        // 都道府県コードに基づく条件文作成
        $this->setCondPrefCd($params);

        $sql .= "";
		$sql .= " SELECT";
		$sql .= "         T57_ENSEN_SHIKUCHOUSON_CD AS ENSEN_CD";
		$sql .= "         ,M02_ENSEN_KANA AS ENSEN_KANA";
		$sql .= "         ,T57_ENSEN_SHIKUCHOUSON_NAME AS ENSEN_NAME";
		$sql .= "         ,SUM(T57_ENSEN_SHIKUCHOUSON_CNT) AS DATACNT";
		$sql .= "     FROM";
		$sql .= "         M02ENSEN";
		$sql .= "    ,T57ENSEN_SHIKUCHOUSON_SUM";
		$sql .= "     WHERE";
		$sql .=           $this->strCheckSQLT57;
		$sql .= "         T57_ENSEN_SHIKUCHOUSON_FLAG = 0";
		$sql .= "         AND T57_DATA_KUBUN = 0";
		$sql .= "         AND T57_ENSEN_SHIKUCHOUSON_CD = M02_ENSEN_CD";
		$sql .= "         AND T57_ENSEN_SHIKUCHOUSON_CNT <> 0";
		$sql .= "     GROUP BY";
		$sql .= "         T57_ENSEN_SHIKUCHOUSON_CD";
		$sql .= "         ,M02_ENSEN_KANA";
		$sql .= "         ,T57_ENSEN_SHIKUCHOUSON_NAME";
		$sql .= "     ORDER BY";
		$sql .= "         M02_ENSEN_KANA";

        return $sql;
	}

	/**
     * sql文生成（OemCdあり）
     * @param $params
     * @param $db_params
     * @return sql文
     */
    protected function generateOemSql($params, &$db_params)
    {
    	// OemCdに基づく条件文を生成
        $strWhereOemM05 = $this->generateCondOem($params);

        // 都道府県コードに基づく条件文作成
        $this->setCondPrefCd($params);

        $sql .= " SELECT";
		$sql .= "         A.M02_ENSEN_CD AS ENSEN_CD";
		$sql .= "         ,A.M02_ENSEN_NAME AS ENSEN_NAME";
		$sql .= "         ,A.M02_ENSEN_KANA AS ENSEN_KANA";
		$sql .= "         ,SUM(C.REC_CNT) AS DATACNT";
		$sql .= "     FROM";
		$sql .= "         M02ENSEN A";
		$sql .= "         ,M05TENPO_INFO B";
		$sql .= "         ,(";
		$sql .= "             SELECT";
		$sql .= "                     C1.M03_TODOUFUKEN_CD AS M03_TODOUFUKEN_CD";
		$sql .= "                     ,C1.M03_ENSEN_CD AS M03_ENSEN_CD";
		$sql .= "                     ,D1.M05_TENPO_CD AS TGT_TENPO_CD";
		$sql .= "                     ,COUNT(*) AS REC_CNT";
		$sql .= "                 FROM";
		$sql .= "                     T03WEB_HEYA B1";
		$sql .= "                     ,M03EKI C1";
		$sql .= "                     ,M05TENPO_INFO D1";
		$sql .= "                 WHERE";
		$sql .= "                     B1.T03_EKI_CD01 = C1.M03_EKI_CD";
		$sql .= "                     AND B1.T03_EKI_CD01 IS NOT NULL";
		$sql .= "                     AND B1.T03_RENRAKU_EKI_CD01 IS NULL";
		$sql .=                       $this->strCheckSQLM03;
		$sql .=                       $strWhereOemM05;
		$sql .= "                     AND B1.T03_TENPO_CD = D1.M05_TENPO_CD";
		if(!empty($params['shikireizero']) && $params['shikireizero'] == "on"){
			$sql .= "                 AND T03_KASHIDASHI_KUBUN = 1 ";
		}
		$sql .= "                 GROUP BY";
		$sql .= "                     C1.M03_TODOUFUKEN_CD";
		$sql .= "                     ,C1.M03_ENSEN_CD";
		$sql .= "                     ,D1.M05_TENPO_CD";
		$sql .= "             UNION ALL";
		$sql .= "             SELECT";
		$sql .= "                     C1.M03_TODOUFUKEN_CD AS M03_TODOUFUKEN_CD";
		$sql .= "                     ,C1.M03_ENSEN_CD AS M03_ENSEN_CD";
		$sql .= "                     ,D1.M05_TENPO_CD AS TGT_TENPO_CD";
		$sql .= "                     ,COUNT(*) AS REC_CNT";
		$sql .= "                 FROM";
		$sql .= "                     T03WEB_HEYA B1";
		$sql .= "                     ,M03EKI C1";
		$sql .= "                     ,M05TENPO_INFO D1";
		$sql .= "                 WHERE";
		$sql .= "                     B1.T03_EKI_CD02 = C1.M03_EKI_CD";
		$sql .= "                     AND B1.T03_EKI_CD02 IS NOT NULL";
		$sql .= "                     AND B1.T03_RENRAKU_EKI_CD02 IS NULL";
		$sql .=                       $this->strCheckSQLM03;
		$sql .=                       $strWhereOemM05;
		$sql .= "                     AND B1.T03_TENPO_CD = D1.M05_TENPO_CD";
		if(!empty($params['shikireizero']) && $params['shikireizero'] == "on"){
			$sql .= "                 AND T03_KASHIDASHI_KUBUN = 1 ";
		}
		$sql .= "                 GROUP BY";
		$sql .= "                     C1.M03_TODOUFUKEN_CD";
		$sql .= "                     ,C1.M03_ENSEN_CD";
		$sql .= "                     ,D1.M05_TENPO_CD";
		$sql .= "             UNION ALL";
		$sql .= "             SELECT";
		$sql .= "                     C1.M03_TODOUFUKEN_CD AS M03_TODOUFUKEN_CD";
		$sql .= "                     ,C1.M03_ENSEN_CD AS M03_ENSEN_CD";
		$sql .= "                     ,D1.M05_TENPO_CD AS TGT_TENPO_CD";
		$sql .= "                     ,COUNT(*) AS REC_CNT";
		$sql .= "                 FROM";
		$sql .= "                     T03WEB_HEYA B1";
		$sql .= "                     ,M03EKI C1";
		$sql .= "                     ,M05TENPO_INFO D1";
		$sql .= "                 WHERE";
		$sql .= "                     B1.T03_EKI_CD03 = C1.M03_EKI_CD";
		$sql .= "                     AND B1.T03_EKI_CD03 IS NOT NULL";
		$sql .= "                     AND B1.T03_RENRAKU_EKI_CD03 IS NULL";
		$sql .=                       $this->strCheckSQLM03;
		$sql .=                       $strWhereOemM05;
		$sql .= "                     AND B1.T03_TENPO_CD = D1.M05_TENPO_CD";
		if(!empty($params['shikireizero']) && $params['shikireizero'] == "on"){
			$sql .= "                 AND T03_KASHIDASHI_KUBUN = 1 ";
		}
		$sql .= "                 GROUP BY";
		$sql .= "                     C1.M03_TODOUFUKEN_CD";
		$sql .= "                     ,C1.M03_ENSEN_CD";
		$sql .= "                     ,D1.M05_TENPO_CD";
		$sql .= "             UNION ALL";
		$sql .= "             SELECT";
		$sql .= "                     C1.M03_TODOUFUKEN_CD AS M03_TODOUFUKEN_CD";
		$sql .= "                     ,C1.M03_ENSEN_CD AS M03_ENSEN_CD";
		$sql .= "                     ,C2.T63_TENPO_CD AS TGT_TENPO_CD";
		$sql .= "                     ,SUM(REC_CNT) AS REC_CNT";
		$sql .= "                 FROM";
		$sql .= "                     M03EKI C1";
		$sql .= "                     ,(";
		$sql .= "                         SELECT";
		$sql .= "                                 DISTINCT B2.M03_EKI_CD";
		$sql .= "                                 ,B3.T63_TENPO_CD";
		$sql .= "                                 ,REC_CNT";
		$sql .= "                             FROM";
		$sql .= "                                 M03EKI B2";
		$sql .= "                                 ,(";
		$sql .= "                                     SELECT";
		$sql .= "                                             T63_RENRAKU_CD";
		$sql .= "                                             ,T63_TENPO_CD";
		$sql .= "                                             ,SUM(T63_COUNT) AS REC_CNT";
		$sql .= "                                         FROM";
		$sql .= "                                             T63RENRAKU_EKI_SUM";
		$sql .= "                                         WHERE";
		if(!empty($params['shikireizero']) && $params['shikireizero'] == "on"){
			$sql .= "                                         T63_DATA_KUBUN = 1 ";
		}else{
			$sql .= "                                         T63_DATA_KUBUN = 0";
		}
		$sql .= "                                         GROUP BY";
		$sql .= "                                             T63_RENRAKU_CD";
		$sql .= "                                             ,T63_TENPO_CD";
		$sql .= "                                 ) B3";
		$sql .= "                                 ,M05TENPO_INFO B4";
		$sql .= "                             WHERE";
		$sql .= "                                 B3.T63_RENRAKU_CD = B2.M03_RENRAKU_EKI_CD";
		$sql .=                                   $this->strCheckSQLM03;
		$sql .=                                   $strWhereOemM05;
		$sql .= "                                 AND B3.T63_TENPO_CD = B4.M05_TENPO_CD";
		$sql .= "                                 AND B2.M03_RENRAKU_EKI_CD IS NOT NULL";
		$sql .= "                     ) C2";
		$sql .= "                 WHERE";
		$sql .= "                     C1.M03_EKI_CD = C2.M03_EKI_CD";
		$sql .= "                 GROUP BY";
		$sql .= "                     C1.M03_TODOUFUKEN_CD";
		$sql .= "                     ,C1.M03_ENSEN_CD";
		$sql .= "                     ,C2.T63_TENPO_CD";
		$sql .= "         ) C";
		$sql .= "     WHERE";
		$sql .= "         A.M02_ENSEN_CD = C.M03_ENSEN_CD";
		$sql .= "         AND B.M05_TENPO_CD = C.TGT_TENPO_CD";
		$sql .=           $strWhereOemM05;
		$sql .= "     GROUP BY";
		$sql .= "         A.M02_ENSEN_CD";
		$sql .= "         ,A.M02_ENSEN_NAME";
		$sql .= "         ,A.M02_ENSEN_KANA";
		$sql .= "     ORDER BY";
		$sql .= "         A.M02_ENSEN_KANA ASC";

        return $sql;
    }
}
?>
